﻿Imports System.Text

Namespace StatementSources
    Public Class CreateCourseStatementSource
        Inherits BaseStatementSource

        Public Sub New(name As String, description As String, code As String, createdBy As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("INSERT [dbo].[Courses] ")
            stringBuilder.Append("([Name],[Description],[Code],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm]) ")
            stringBuilder.Append("VALUES (@Name,@Description,@Code,@CreatedBy,GETDATE(),@CreatedBy,GETDATE());")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@Name", name)
            Parameters.AddWithValue("@Description", description)
            Parameters.AddWithValue("@Code", code)
            Parameters.AddWithValue("@CreatedBy", createdBy)
        End Sub

    End Class

    Public Class ReadAllCourseStatementSource
        Inherits BaseStatementSource

        Public Sub New()
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [CourseId],[Name],[Description],[Code],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Courses];")

            Sql = stringBuilder.ToString()
        End Sub

    End Class

    Public Class ReadByCourseIdStatementSource
        Inherits BaseStatementSource

        Public Sub New(courseId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [CourseId],[Name],[Description],[Code],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Courses] ")
            stringBuilder.Append("WHERE [CourseId]=@CourseId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@CourseId", courseId)
        End Sub

    End Class

    Public Class UpdateCourseStatementSource
        Inherits BaseStatementSource

        Public Sub New(courseId As Integer, name As String, description As String, code As String, modifiedBy As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("UPDATE [dbo].[Courses] SET ")
            stringBuilder.Append("[Name]=ISNULL(@Name,[Name]),")
            stringBuilder.Append("[Description]=ISNULL(@Description,[Description]),")
            stringBuilder.Append("[Code]=ISNULL(@Code,[Code]),")
            stringBuilder.Append("[ModifiedBy]=@ModifiedBy,")
            stringBuilder.Append("[ModifiedDttm]=GETDATE() ")
            stringBuilder.Append("WHERE [CourseId]=@CourseId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@CourseId", courseId)
            Parameters.AddWithValue("@Name", name)
            Parameters.AddWithValue("@Description", description)
            Parameters.AddWithValue("@Code", code)
            Parameters.AddWithValue("@ModifiedBy", modifiedBy)
        End Sub

    End Class

    Public Class DeleteCourseStatementSource
        Inherits BaseStatementSource

        Public Sub New(courseId As Integer)
            Sql = "DELETE FROM [dbo].[Courses] WHERE [CourseId]=@CourseId;"
            Parameters.AddWithValue("@CourseId", courseId)
        End Sub

    End Class
End Namespace

